inherited fmDopPodrView: TfmDopPodrView
  Left = 371
  Top = 130
  Width = 623
  Height = 540
  Caption = 'fmDopPodrView'
  Constraints.MinHeight = 540
  Constraints.MinWidth = 623
  OnShow = FormShow
  PixelsPerInch = 96
  TextHeight = 13
  inherited sbBottom: TStatusBar
    Top = 479
    Width = 615
  end
  inherited Panel1: TPanel
    Width = 615
    Visible = False
    inherited BitBtn1: TBitBtn
      Left = 445
    end
    inherited BitBtn2: TBitBtn
      Left = 533
    end
  end
  object Panel2: TPanel [2]
    Left = 0
    Top = 45
    Width = 615
    Height = 84
    Align = alTop
    TabOrder = 2
    object Label7: TLabel
      Left = 16
      Top = 12
      Width = 173
      Height = 13
      Caption = #1054#1073#1088#1072#1079#1086#1074#1072#1090#1077#1083#1100#1085#1086#1077' '#1087#1086#1076#1088#1072#1079#1076#1077#1083#1077#1085#1080#1077
      Font.Charset = DEFAULT_CHARSET
      Font.Color = clGray
      Font.Height = -11
      Font.Name = 'Tahoma'
      Font.Style = []
      ParentFont = False
    end
    object Label1: TLabel
      Left = 336
      Top = 12
      Width = 37
      Height = 13
      Caption = #1057#1077#1082#1094#1080#1103
      Font.Charset = DEFAULT_CHARSET
      Font.Color = clGray
      Font.Height = -11
      Font.Name = 'Tahoma'
      Font.Style = []
      ParentFont = False
    end
    object dxLookupTreeView1: TdxLookupTreeView
      Left = 16
      Top = 32
      Width = 273
      Height = 27
      CanSelectParents = False
      DropDownRows = 30
      Font.Charset = DEFAULT_CHARSET
      Font.Color = clWindowText
      Font.Height = -16
      Font.Name = 'Tahoma'
      Font.Style = []
      ParentColor = False
      ParentFont = False
      TabOrder = 0
      TabStop = True
      TreeViewColor = clWindow
      TreeViewCursor = crDefault
      TreeViewFont.Charset = DEFAULT_CHARSET
      TreeViewFont.Color = clWindowText
      TreeViewFont.Height = -11
      TreeViewFont.Name = 'MS Sans Serif'
      TreeViewFont.Style = []
      TreeViewIndent = 19
      TreeViewReadOnly = False
      TreeViewShowButtons = True
      TreeViewShowHint = False
      TreeViewShowLines = True
      TreeViewShowRoot = True
      TreeViewSortType = stNone
      OnCloseUp = dxLookupTreeView1CloseUp
      DisplayField = 'SDNAME'
      DividedChar = '.'
      ListSource = dsSD
      KeyField = 'SDID'
      ListField = 'SDNAME'
      Options = [trDBCanDelete, trDBConfirmDelete, trCanDBNavigate, trCheckHasChildren]
      ParentField = 'SDPARID'
      RootValue = 0
      StateIndexField = 'SDISDELETE'
      TextStyle = tvtsShort
      Alignment = taLeftJustify
    end
    object dxLookupTreeView2: TdxLookupTreeView
      Left = 336
      Top = 32
      Width = 273
      Height = 27
      CanSelectParents = False
      DropDownRows = 30
      Font.Charset = DEFAULT_CHARSET
      Font.Color = clWindowText
      Font.Height = -16
      Font.Name = 'Tahoma'
      Font.Style = []
      ParentColor = False
      ParentFont = False
      TabOrder = 1
      TabStop = True
      TreeViewColor = clWindow
      TreeViewCursor = crDefault
      TreeViewFont.Charset = DEFAULT_CHARSET
      TreeViewFont.Color = clWindowText
      TreeViewFont.Height = -11
      TreeViewFont.Name = 'MS Sans Serif'
      TreeViewFont.Style = []
      TreeViewIndent = 19
      TreeViewReadOnly = False
      TreeViewShowButtons = True
      TreeViewShowHint = False
      TreeViewShowLines = True
      TreeViewShowRoot = True
      TreeViewSortType = stNone
      OnCloseUp = dxLookupTreeView1CloseUp
      DisplayField = 'SDNAME'
      DividedChar = '.'
      ListSource = dsSD1
      KeyField = 'SDID'
      ListField = 'SDNAME'
      Options = [trDBCanDelete, trDBConfirmDelete, trCanDBNavigate, trCheckHasChildren]
      ParentField = 'SDPARID'
      RootValue = 0
      StateIndexField = 'SDISDELETE'
      TextStyle = tvtsShort
      Alignment = taLeftJustify
    end
  end
  object dbgRSTabel: TcxGrid [3]
    Left = 0
    Top = 129
    Width = 615
    Height = 350
    Align = alClient
    Font.Charset = DEFAULT_CHARSET
    Font.Color = clWindowText
    Font.Height = -13
    Font.Name = 'Tahoma'
    Font.Style = []
    ParentFont = False
    TabOrder = 3
    LookAndFeel.Kind = lfStandard
    object tvRSTabel: TcxGridDBTableView
      NavigatorButtons.ConfirmDelete = False
      NavigatorButtons.PriorPage.Visible = True
      NavigatorButtons.Next.Visible = True
      NavigatorButtons.Insert.Visible = True
      NavigatorButtons.Append.Visible = False
      NavigatorButtons.Edit.Visible = False
      NavigatorButtons.Post.Visible = True
      NavigatorButtons.Cancel.Visible = False
      NavigatorButtons.Refresh.Visible = False
      NavigatorButtons.SaveBookmark.Visible = False
      NavigatorButtons.GotoBookmark.Visible = False
      NavigatorButtons.Filter.Visible = False
      DataController.DataSource = dsChildren
      DataController.Summary.DefaultGroupSummaryItems = <>
      DataController.Summary.FooterSummaryItems = <
        item
          Kind = skCount
          FieldName = 'PP'
          Column = tvRSTabelPP
        end>
      DataController.Summary.SummaryGroups = <>
      OptionsBehavior.CellHints = True
      OptionsCustomize.ColumnFiltering = False
      OptionsData.Appending = True
      OptionsData.CancelOnExit = False
      OptionsView.CellEndEllipsis = True
      OptionsView.NoDataToDisplayInfoText = '<'#1053#1077#1090' '#1076#1072#1085#1085#1099#1093'>'
      OptionsView.CellAutoHeight = True
      OptionsView.CellTextMaxLineCount = 1
      OptionsView.ColumnAutoWidth = True
      OptionsView.Footer = True
      OptionsView.GroupByBox = False
      OptionsView.HeaderEndEllipsis = True
      OptionsView.Indicator = True
      Preview.Visible = True
      Styles.Header = DMInv.cxSiniyText
      object tvRSTabelColumn1: TcxGridDBColumn
        Caption = #8470
        OnGetDataText = tvRSTabelColumn1GetDataText
        Width = 26
      end
      object tvRSTabelPP: TcxGridDBColumn
        Caption = #1060#1048#1054
        DataBinding.FieldName = 'PP'
        Width = 433
      end
      object tvRSTabelZAKAZ: TcxGridDBColumn
        Caption = #1047#1072#1082#1072#1079#1072#1085#1086
        DataBinding.FieldName = 'ZAKAZ'
        Width = 82
      end
      object tvRSTabelPROVELI: TcxGridDBColumn
        Caption = #1055#1088#1077#1076#1086#1089#1090#1072#1074#1083#1077#1085#1086
        DataBinding.FieldName = 'PROVELI'
        Width = 129
      end
    end
    object cxGridLevel2: TcxGridLevel
      GridView = tvRSTabel
    end
  end
  object dsSD: TDataSource
    DataSet = qSD
    Left = 224
    Top = 96
  end
  object qSD: TIBQuery
    Database = DMInv.DBRead
    Transaction = DMInv.IBTransaction1
    SQL.Strings = (
      'SELECT t1.*, SD1.SDISDELETE'
      'FROM'
      '('
      'WITH   recursive'
      '   tree (SDNAME, SDID, SDPARID, o)'
      
        '   AS (SELECT SD.SDNAME, SD.SDID, sd.SDPARID, CAST('#39#39' AS VARCHAR' +
        '(2048)) AS o FROM SYS_DIC SD'
      '      WHERE sd.SDPARID = 13'
      '      UNION ALL'
      '      SELECT SD1.SDNAME, SD1.SDID, sd1.SDPARID, '#39#39' || o'
      ''
      '      FROM SYS_DIC SD1'
      '          INNER JOIN tree t '
      '           ON t.sdid = sd1.SDPARID)'
      ' SELECT  o || SDNAME AS SDNAME, SDID, SDPARID FROM tree t'
      ') AS t1'
      'INNER JOIN SYS_DIC sd1 ON sd1.SDID = t1.sdid'
      '--order by 1'
      '')
    GeneratorField.Field = 'SDID'
    Left = 264
    Top = 96
    object qSDSDID: TIntegerField
      FieldName = 'SDID'
      ProviderFlags = []
    end
    object qSDSDPARID: TIntegerField
      FieldName = 'SDPARID'
      ProviderFlags = []
    end
    object qSDSDNAME: TIBStringField
      DisplayLabel = #1069#1083#1077#1084#1077#1085#1090
      FieldName = 'SDNAME'
      ProviderFlags = []
      Size = 2304
    end
    object qSDSDISDELETE: TIntegerField
      DisplayLabel = #1059#1076#1072#1083#1080#1090#1100'?'
      FieldName = 'SDISDELETE'
      Origin = '"SYS_DIC"."SDISDELETE"'
    end
  end
  object qChildren: TIBQuery
    Database = DMInv.DBRead
    Transaction = DMInv.IBTransaction1
    BeforeOpen = qChildrenBeforeOpen
    SQL.Strings = (
      
        'SELECT P.PID, (COALESCE(P.PLASTNAME,'#39' '#39')||'#39' '#39'||COALESCE(P.PNAME,' +
        #39' '#39')||'#39' '#39'||COALESCE(P.PPATRONYMIC,'#39' '#39')) AS PP,'
      
        '    SD.SDNAME, sd1.SDNAME AS podr, P.PDATEBIRTH, COALESCE(zakaz_' +
        'uslugi.zakazali, 0) AS zakaz, COALESCE(okaz_uslugi.proveli, 0) a' +
        's proveli'
      'FROM PEOPLE P'
      'LEFT OUTER JOIN P_STATUS PS ON PS.PID = P.PID'
      
        'LEFT OUTER JOIN DETI_PODRAZD DP ON DP.PID = P.PID AND CURRENT_DA' +
        'TE BETWEEN CAST(dp.DPDATEIN AS DATE) AND COALESCE(CAST(dp.DPDATE' +
        'OUT AS DATE), CURRENT_DATE+3650)'
      'LEFT OUTER JOIN SYS_DIC SD1 ON SD1.SDID = DP.SDID'
      'LEFT OUTER JOIN SYS_DIC SD ON SD.SDID = PS.SDID'
      'LEFT OUTER JOIN ('
      'SELECT RSP.PID, COUNT (RSP.PID) AS proveli'
      'FROM RENDERING_SERVICES RS'
      'INNER JOIN RS_PEOPLE RSP ON RSP.RSID = RS.RSID'
      'WHERE RS.SDID = :SDID_usl'
      '    AND RS.RSISDELETE IS NULL'
      '    AND RSP.RSPISDELETE IS NULL'
      'GROUP BY 1'
      ') AS okaz_uslugi ON okaz_uslugi.PID = P.PID'
      'LEFT OUTER JOIN ('
      '    SELECT APU.PID, SUM(S.SCOUNT) AS zakazali'
      '    FROM SERVICES S'
      '    INNER JOIN SERVICES_TYPE ST ON ST.STID = S.STID'
      '    INNER JOIN SPEC_COND SC ON SC.SCID = S.SCID'
      '    INNER JOIN AGREEMENTS A ON a.AGID = SC.AGID'
      '    INNER JOIN AGR_POLUCH_USLUG APU ON APU.AGID = A.AGID'
      '    WHERE ST.STSDIDTABEL = :SDID_usl'
      '    AND sc.scisdelete IS NULL'
      '    AND a.agisdelete IS NULL'
      '    AND ST.STTYPE = 0'
      '    GROUP BY 1'
      ') AS zakaz_uslugi ON zakaz_uslugi.PID = P.PID'
      'WHERE P.PISDELETE IS NULL'
      '    AND PS.SDID = 1246'
      
        '    AND  (zakaz_uslugi.zakazali - COALESCE(okaz_uslugi.proveli, ' +
        '0)) > 0'
      '    AND DP.SDID = :SDID_podr'
      'UNION'
      
        'SELECT P.PID, (COALESCE(P.PLASTNAME,'#39' '#39')||'#39' '#39'||COALESCE(P.PNAME,' +
        #39' '#39')||'#39' '#39'||COALESCE(P.PPATRONYMIC,'#39' '#39')) AS PP,'
      
        '    SD.SDNAME, sd1.SDNAME AS podr, P.PDATEBIRTH, CAST(NULL AS IN' +
        'TEGER) AS zakaz,'
      '    CAST(NULL AS INTEGER) as proveli'
      'FROM PEOPLE P'
      'LEFT OUTER JOIN P_STATUS PS ON PS.PID = P.PID'
      
        'LEFT OUTER JOIN DETI_PODRAZD DP ON DP.PID = P.PID AND CURRENT_DA' +
        'TE BETWEEN CAST(dp.DPDATEIN AS DATE) AND COALESCE(CAST(dp.DPDATE' +
        'OUT AS DATE), CURRENT_DATE+3650)'
      'LEFT OUTER JOIN SYS_DIC SD1 ON SD1.SDID = DP.SDID'
      'LEFT OUTER JOIN SYS_DIC SD ON SD.SDID = PS.SDID'
      'INNER JOIN ('
      '    SELECT APU.PID'
      '    FROM SERVICES S'
      '    INNER JOIN SERVICES_TYPE ST ON ST.STID = S.STID'
      '    INNER JOIN SPEC_COND SC ON SC.SCID = S.SCID'
      '    INNER JOIN AGREEMENTS A ON a.AGID = SC.AGID'
      '    INNER JOIN AGR_POLUCH_USLUG APU ON APU.AGID = A.AGID'
      '    WHERE ST.STSDIDTABEL = :SDID_usl'
      '    AND sc.scisdelete IS NULL'
      '    AND a.agisdelete IS NULL'
      '    AND ST.STTYPE = 2'
      '    AND CURRENT_DATE BETWEEN SC.SCDATEBEGIN AND SC.SCDATEEND'
      '    GROUP BY 1'
      ') AS zakaz_uslugi ON zakaz_uslugi.PID = P.PID'
      'WHERE P.PISDELETE IS NULL'
      '    AND PS.SDID = 1246'
      '    AND DP.SDID = :SDID_podr'
      'ORDER BY 2'
      '')
    Left = 264
    Top = 176
    ParamData = <
      item
        DataType = ftUnknown
        Name = 'SDID_usl'
        ParamType = ptUnknown
      end
      item
        DataType = ftUnknown
        Name = 'SDID_usl'
        ParamType = ptUnknown
      end
      item
        DataType = ftUnknown
        Name = 'SDID_podr'
        ParamType = ptUnknown
      end
      item
        DataType = ftUnknown
        Name = 'SDID_usl'
        ParamType = ptUnknown
      end
      item
        DataType = ftUnknown
        Name = 'SDID_podr'
        ParamType = ptUnknown
      end>
    object qChildrenPID: TIntegerField
      FieldName = 'PID'
      Origin = '"PEOPLE"."PID"'
      ProviderFlags = [pfInUpdate, pfInWhere, pfInKey]
      Required = True
    end
    object qChildrenPP: TIBStringField
      FieldName = 'PP'
      ProviderFlags = []
      Size = 767
    end
    object qChildrenSDNAME: TIBStringField
      FieldName = 'SDNAME'
      Origin = '"SYS_DIC"."SDNAME"'
      Size = 255
    end
    object qChildrenPODR: TIBStringField
      FieldName = 'PODR'
      Origin = '"SYS_DIC"."SDNAME"'
      Size = 255
    end
    object qChildrenPDATEBIRTH: TDateField
      FieldName = 'PDATEBIRTH'
      Origin = '"PEOPLE"."PDATEBIRTH"'
    end
    object qChildrenZAKAZ: TLargeintField
      FieldName = 'ZAKAZ'
      ProviderFlags = []
    end
    object qChildrenPROVELI: TIntegerField
      FieldName = 'PROVELI'
      ProviderFlags = []
    end
  end
  object dsChildren: TDataSource
    DataSet = qChildren
    Left = 232
    Top = 176
  end
  object qSD1: TIBQuery
    Database = DMInv.DBRead
    Transaction = DMInv.IBTransaction1
    SQL.Strings = (
      'SELECT t1.*, SD1.SDISDELETE'
      'FROM'
      '('
      'WITH   recursive'
      '   tree (SDNAME, SDID, SDPARID, o)'
      
        '   AS (SELECT SD.SDNAME, SD.SDID, sd.SDPARID, CAST('#39#39' AS VARCHAR' +
        '(2048)) AS o FROM SYS_DIC SD'
      '      WHERE sd.SDPARID = 20'
      '      UNION ALL'
      '      SELECT SD1.SDNAME, SD1.SDID, sd1.SDPARID, '#39#39' || o'
      ''
      '      FROM SYS_DIC SD1'
      '          INNER JOIN tree t '
      '           ON t.sdid = sd1.SDPARID)'
      ' SELECT  o || SDNAME AS SDNAME, SDID, SDPARID FROM tree t'
      ') AS t1'
      'INNER JOIN SYS_DIC sd1 ON sd1.SDID = t1.sdid'
      '--order by 1'
      '')
    GeneratorField.Field = 'SDID'
    Left = 440
    Top = 96
    object qSD1SDNAME: TIBStringField
      FieldName = 'SDNAME'
      ProviderFlags = []
      Size = 2303
    end
    object qSD1SDID: TIntegerField
      FieldName = 'SDID'
      ProviderFlags = []
    end
    object qSD1SDPARID: TIntegerField
      FieldName = 'SDPARID'
      ProviderFlags = []
    end
    object qSD1SDISDELETE: TIntegerField
      FieldName = 'SDISDELETE'
      Origin = '"SYS_DIC"."SDISDELETE"'
    end
  end
  object dsSD1: TDataSource
    DataSet = qSD1
    Left = 400
    Top = 96
  end
end
